package klex.eserver.dao.pricemanager.channel.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import klex.eserver.dao.BaseDao;
import klex.eserver.dao.pricemanager.channel.ChannelClassDao;
import klex.eserver.entity.OnlineClientPClass;

public class ChannelClassDaoImpl extends BaseDao implements ChannelClassDao {

	public Integer addChannelClass(OnlineClientPClass channelClass) {
		return (Integer) getHibernateTemplate().save(channelClass);
	}

	public OnlineClientPClass findChannelClassByNumber(String number) {
		OnlineClientPClass channClass = new OnlineClientPClass();
		String sql = "select * from kexekey.ONLINE_CLIENT_P_CLASS cl where cl.P_CLASS_NUMBER = ?";
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, number);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				channClass.setPClassId(rs.getInt("P_CLASS_ID"));
				channClass.setPClassNumber(rs.getString("P_CLASS_NUMBER"));
				channClass.setPClassName(rs.getString("P_CLASS_NAME"));
				channClass.setPClassNote(rs.getString("P_CLASS_NOTE"));
				channClass.setPClassAddDate(rs.getDate("P_CLASS_ADD_DATE"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection(conn, pstmt, rs);
		}
		return channClass;
	}

	public void modifyChannelClass(OnlineClientPClass channelClass) {
		getHibernateTemplate().update(channelClass);
	}

	public List<OnlineClientPClass> findChannelClass(
			OnlineClientPClass chanClass, String orderField,
			String orderDirection) {
		List<OnlineClientPClass> list = new ArrayList<OnlineClientPClass>();
		StringBuffer sql = new StringBuffer(
				"select * from kexekey.ONLINE_CLIENT_P_CLASS cl where 1=1 ");
		String[] args = new String[2];
		int i = 0;
		if (null != chanClass) {
			if (null != chanClass.getPClassName()
					&& !(chanClass.getPClassName().trim()).equals("")) {
				sql.append(" and cl.P_CLASS_NAME like ? ");
				args[i] = "%" + chanClass.getPClassName() + "%";
				i++;
			}
			if (null != chanClass.getPClassNumber()
					&& !(chanClass.getPClassNumber().trim()).equals("")) {
				sql.append(" and cl.P_CLASS_NUMBER like ? ");
				args[i] = "%" + chanClass.getPClassNumber() + "%";
				i++;
			}
		}
		sql.append(" order by "
				+ (!"".equals(orderField) ? orderField : "P_CLASS_ID") + " "
				+ (!"".equals(orderDirection) ? orderDirection : "asc"));
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql.toString());
			int j = 0;
			for (j = 0; j < i; j++) {
				pstmt.setString(j + 1, args[j]);
			}
			rs = pstmt.executeQuery();
			OnlineClientPClass channelClass = null;
			while (rs.next()) {
				channelClass = new OnlineClientPClass();
				channelClass.setPClassId(rs.getInt("P_CLASS_ID"));
				channelClass.setPClassNumber(rs.getString("P_CLASS_NUMBER"));
				channelClass.setPClassName(rs.getString("P_CLASS_NAME"));
				channelClass.setPClassNote(rs.getString("P_CLASS_NOTE"));
				channelClass.setPClassAddDate(rs.getDate("P_CLASS_ADD_DATE"));
				list.add(channelClass);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection(conn, pstmt, rs);
		}
		return list;
	}

	// public PageUtil<OnlineClientPClass> findChannelClass(
	// OnlineClientPClass chanClass, int pageNo, int pageSize) {
	// PageUtil<OnlineClientPClass> pageUtil = new
	// PageUtil<OnlineClientPClass>();
	// pageUtil.setPageNo(pageNo);
	// pageUtil.setPageSize(pageSize);
	// List<OnlineClientPClass> list = new ArrayList<OnlineClientPClass>();
	// StringBuffer sql = new StringBuffer(
	// "select * from (select t.*,(rownum)rn from (select * from kexekey.ONLINE_CLIENT_P_CLASS cl where 1=1 ");
	// String[] args = new String[2];
	// int i = 0;
	// if (null != chanClass) {
	// if (null != chanClass.getPClassName()
	// && !(chanClass.getPClassName().trim()).equals("")) {
	// sql.append(" and cl.P_CLASS_NAME = ? ");
	// i++;
	// args[i] = "%" + chanClass.getPClassName() + "%";
	// }
	// if (null != chanClass.getPClassNumber()
	// && !(chanClass.getPClassNumber().trim()).equals("")) {
	// sql.append(" and cl.P_CLASS_NUMBER = ? ");
	// i++;
	// args[i] = "%" + chanClass.getPClassNumber() + "%";
	// }
	// }
	// sql.append(" ) t where rownum <= ? ) rn > ?");
	// Connection conn = getConnection();
	// PreparedStatement pstmt = null;
	// ResultSet rs = null;
	// try {
	// pstmt = conn.prepareStatement(sql.toString());
	// int j = 0;
	// for (j = 0; j < i; j++) {
	// pstmt.setString(j + 1, args[j]);
	// }
	// pstmt.setInt(j + 1, pageNo * pageSize);
	// pstmt.setInt(j + 2, (pageNo - 1) * pageSize);
	// rs = pstmt.executeQuery();
	// OnlineClientPClass channelClass = null;
	// while (rs.next()) {
	// channelClass = new OnlineClientPClass();
	// channelClass.setPClassId(rs.getInt("P_CLASS_ID"));
	// channelClass.setPClassNumber(rs.getString("P_CLASS_NUMBER"));
	// channelClass.setPClassName(rs.getString("P_CLASS_NAME"));
	// channelClass.setPClassNote(rs.getString("P_CLASS_NOTE"));
	// channelClass.setPClassAddDate(rs.getDate("P_CLASS_ADD_DATE"));
	// list.add(channelClass);
	// }
	// pageUtil.setList(list);
	// String subSql =
	// "select count(*) count from kexekey.ONLINE_CLIENT_P_CLASS cl";
	// pstmt = conn.prepareStatement(subSql);
	// rs = pstmt.executeQuery();
	// if (rs.next()) {
	// pageUtil.setTotalRecords(rs.getInt("count"));
	// }
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// } finally {
	// closeConnection(conn, pstmt, rs);
	// }
	// return pageUtil;
	// }

}
